package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SurgeryDao {
    public SurgeryDao() {
    }

    public boolean creaSurgey(String sala, int staffn,
                              int appointmentn) {

        int numberS=-5;
        Conexion conexion = new Conexion();
        boolean creado = false;
        Connection conex;

        try {
            conex = conexion.getConnection();
            PreparedStatement ps = null;
            ResultSet rs = null;

            ResultSet rsa = null;
            int mayor = -1;
            ps = conex.prepareStatement("select max (Surgerynumber)  from Surgery");

            rsa = ps.executeQuery();
            if (rsa != null) {
                while (rsa.next()) {
                    mayor = rsa.getInt(1);
                }
            } else
                numberS = 1;
        
                ps =
               conex.prepareStatement("INSERT INTO SURGERY(SurgeryNumber,Sala,staffNumber,AppoinmentNumber) VALUES (?,?,?,?)");
                
            numberS = mayor + 1;
            ps.setInt(1, numberS);
            ps.setString(2, sala);
            ps.setInt(3, staffn);
            ps.setInt(4, appointmentn);
            
            int numeroFilas = ps.executeUpdate();
            System.out.println("El numero de filas que inserto fueron" +
                               numeroFilas);
            creado = true;
            conex.close();

        } catch (SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());

        }
        return creado;
    }
    
    public boolean eliminarSurgery(int numberS) {

      Conexion conexion = new Conexion();
      boolean eliminado = false;
      Connection conex;

      try {
        conex = conexion.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        ps = conex.prepareStatement("DELETE FROM Surgery where SurgeryNumber = ?");

        ps.setInt(1, numberS);


        int numeroFilas = ps.executeUpdate();
        System.out.println("El numero de filas que borro fueron" +
                           numeroFilas);
        eliminado = true;
        conex.close();

      } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());

      }
      return eliminado;
    }
    
    public boolean modificaSurgerySala(int numberS,  String sala)
    {
      Conexion conexion = new Conexion();
      Connection conex;
      try {
        conex = conexion.getConnection();
        PreparedStatement ps = null;
        
        ps =
            conex.prepareStatement("UPDATE surgery set sala = ?  where SurgeryNumber = ?"); 
        
        ps.setString(1, sala);
        
        ps.setInt(2, numberS);
        
        int numeroFilas = ps.executeUpdate();
        System.out.println("El numero de filas que actualizo fueron" +
                           numeroFilas);

        conex.close();
        
      } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        return false;
      }
      return true;
    }
    
    public boolean modificaSurgeryStaffNumber(int numberS,  int StaffNumber)
    {
      Conexion conexion = new Conexion();
      Connection conex;
      try {
        conex = conexion.getConnection();
        PreparedStatement ps = null;
        
        ps =
            conex.prepareStatement("UPDATE surgery set StaffNumber = ?  where SurgeryNumber = ?"); 
        
        ps.setInt(1, StaffNumber);
        
        ps.setInt(2, numberS);
        
        int numeroFilas = ps.executeUpdate();
        System.out.println("El numero de filas que actualizo fueron" +
                           numeroFilas);

        conex.close();
        
      } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        return false;
      }
      return true;
    }
    
    public boolean modificaSurgeryAppointmentNumber(int numberS,  int appointmentN)
    {
      Conexion conexion = new Conexion();
      Connection conex;
      try {
        conex = conexion.getConnection();
        PreparedStatement ps = null;
        
        ps =
            conex.prepareStatement("UPDATE surgery set AppoinmentNumber = ?  where SurgeryNumber = ?"); 
        
        ps.setInt(1, appointmentN);
        
        ps.setInt(2, numberS);
        
        int numeroFilas = ps.executeUpdate();
        System.out.println("El numero de filas que actualizo fueron" +
                           numeroFilas);

        conex.close();
        
      } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        return false;
      }
      return true;
    }
    
  public Object[][] DatosCirugiaTabla() {
    Conexion conexion = new Conexion();
    Connection conex;
    //int numeroFilas = 0;
    //int numeroColumnas = 0;
    Object[][] datos = new Object[100][100];

    try {
      conex = conexion.getConnection();
      PreparedStatement ps = null;
      ResultSet rs = null;

      ps = conex.prepareStatement("select * FROM Surgery ");     
      int i = 0;
      int j = 0;
      datos[i][j]= " APPOINMENT NUMBER";
      j++;
      datos[i][j]="DATE";
      j++;
      datos[i][j]="TYPEA";
      j++;
      datos[i][j]= "CLINIC NUMBER";
      j++;
      datos[i][j]="OWNER NAME";
      j++;
      datos[i][j]= "HOUR START";
      j++;
      datos[i][j]="MIN START";
      j++;
      datos[i][j]="DURACION";
      j++;
      datos[i][j]="PET ID";
      i++;
      j=0;
 
      rs = ps.executeQuery();
      System.out.println(rs + " es el resultado de la consulta !");

      while (rs.next()) {
        datos[i][j] = rs.getInt(1);
        System.out.print(datos[i][j] + " ");
        j++;
        datos[i][j] = rs.getString(2);
        System.out.print(datos[i][j] + " ");
        j++;
        datos[i][j] = rs.getInt(3);
        System.out.print(datos[i][j] + " ");
        j++;
        datos[i][j] = rs.getInt(4);
        System.out.print(datos[i][j] + " "); 
        i++;
        j=0;
      }
      rs.close();
      ps.close();
      conex.close();

      
      /*for (int f = 1; f < 9; f++) {
        for (int g = 0; g < 9; g++) {
          System.out.println("datos");
          System.out.print(datos[f][g]);
        }
      } */  
      
    } catch (SQLException ex) {
      System.err.println("SQLException: " + ex.getMessage());
    }
    return datos;
  }
}
